# -*- coding:utf-8 -*-
# Created by ibf at 2018/11/6 0006
import xml.etree.ElementTree as ET
import sys
import os
import requests
import sqlalchemy
from sqlalchemy import Column, String,Integer,Text,Date,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

def read_xml(url,in_path):
    """读取并解析xml文件
       in_path: xml路径
       return: tree"""
    with open(in_path,'wb') as f:
        f.write(requests.get(url).content)
    tree = ET.parse(in_path)
    return tree

def creat_dict(root):
    """xml生成为dict：，
    将tree中个节点添加到list中，将list转换为字典dict_init
    叠加生成多层字典dict_new"""
    dict_new = {}
    for key, valu in enumerate(root):
        dict_init = {}
        list_init = []
        for item in valu:
            list_init.append([item.tag, item.text])
            for lists in list_init:
                dict_init[lists[0]] = lists[1]
        dict_new[key] = dict_init
    return dict_new

# 创建对象的基类:
Base = declarative_base()

class Zp(Base):
    __tablename__ = 'zp_item'
    id = Column(Integer, primary_key=True)
    zwmc = Column(String(300), nullable=True)
    gsmc = Column(String(300), nullable=True)
    min_zwyx = Column(Integer, nullable=True)
    max_zwyx = Column(Integer,nullable=True)
    dd = Column(String(100),nullable=True)
    fbrq = Column(String(100), nullable=True)
    gsxz = Column(String(100), nullable=True)
    gzjy = Column(String(100), nullable=True)
    xl = Column(String(100), nullable=True)
    zwlb = Column(String(100), nullable=True)
    gsgm = Column(String(100), nullable=True)
    gshy = Column(String(100), nullable=True)
    rzyq = Column(Text, nullable=True)
    href = Column(String(100), nullable=False)
    zwlb_big=Column(String(100), nullable=True)
    zprs = Column(Integer, nullable=True)
    province=Column(String(100), nullable=True)
    pointy=Column(String(100), nullable=True)
    pointx=Column(String(100), nullable=True)
    source=Column(String(100), nullable=True)
    zwyx=Column(String(100), nullable=True)
    type=Column(String(100),nullable=True)
    category=Column(String(100),nullable=True)
    flxx=Column(String(100),nullable=True)
#删除过期备份
rmtmpfile = "rm -f /vdb/python/zwhx/lagou/tmp/*"
#下载并解析拉勾网接口数据
input = "https://www.lagou.com/upload/third_data_interface/newBaidu/2c90efa427153f9e0127153f9eaa0006/dataIndex.xml"
os.system(rmtmpfile)
xml = read_xml(input,in_path='/vdb/python/zwhx/lagou/tmp/lagou.xml')
node = creat_dict(xml.getroot())  # 将xml转换为dict
# 初始化数据库连接:
engine=create_engine('mysql+pymysql://root:FanTan879@127.0.0.1/zw_spider?charset=utf8')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
session=DBSession()
Base.metadata.create_all(engine)

# 找到所有的一级节点 input
# 遍历每个一级节点，得到具体的值
for one in node:
    url = node[one]["loc"]
    file=os.path.join('/vdb/python/zwhx/lagou/tmp',url.split('/')[-1])
    urlset=read_xml(url,in_path=file)
    node_new = urlset.getroot()
    for two in node_new.findall('url'):
        node_dict = two.find('data').find('display')
        zp_obj=Zp()
        zwmc=node_dict.find('title').text
        zp_obj.zwmc = zwmc[:zwmc.find('(')]# 职位名称
        zp_obj.zwlb = node_dict.find('jobsecondclass').text if node_dict.find('jobsecondclass').text else node_dict.find('jobfirstclass').text#职位类别
        zp_obj.rzyq = node_dict.find('description').text#岗位职责#
        zp_obj.xl = node_dict.find('education').text#学历
        zp_obj.gzjy = node_dict.find('experience').text#工作经验#
        zp_obj.fbrq = node_dict.find('startdate').text#发布日期#
        zwyx=node_dict.find('salary').text#薪资#
        zp_obj.zwyx = zwyx
        min_zwyx=max_zwyx=0
        if '元' in zwyx:
            zwyx_list=zwyx.split('-')
            if len(zwyx_list)>2:
                min_zwyx=zwyx_list[0]
                max_zwyx=zwyx_list[1]
            else:
                min_zwyx=max_zwyx=zwyx_list[0]
        elif 'K' in zwyx:
            zwyx_list=zwyx.replace('K','').split('-')
            if len(zwyx_list)>2:
                min_zwyx=zwyx_list[0]+'000'
                max_zwyx=zwyx_list[1]+'000'
            else:
                min_zwyx=max_zwyx=zwyx_list[0]+'000'
        else:
            min_zwyx=max_zwyx=0
        zp_obj.min_zwyx = min_zwyx#最低薪资#
        zp_obj.max_zwyx = max_zwyx#最高薪资#
        zp_obj.dd = node_dict.find('city').text#城市#
        zp_obj.gsmc = node_dict.find('officialname').text#公司名称#
        zp_obj.gsxz = node_dict.find('employertype').text#公司性质#
        zp_obj.gsgm = node_dict.find('size').text#公司规模
        zp_obj.gshy = node_dict.find('industry').text# 公司行业
        zp_obj.zwlb_big = node_dict.find('industry').text# 公司行业
        zp_obj.source = node_dict.find('source').text# 来源
        zp_obj.href = node_dict.find('joblink').text# 链接
        zp_obj.type = node_dict.find('type').text# 职位类型
        zp_obj.zprs = 0# 招聘人数
        zp_obj.flxx = node_dict.find('welfare').text  # 福利信息
        session.add(zp_obj)
    session.commit()

